package com.kelai;

import com.kelai.common.cache.RedisBean;
import com.kelai.common.constants.WebAppConstants;
import com.kelai.domain.*;
import com.kelai.repository.*;
import com.kelai.resource.IResourceLayer;
import com.kelai.utils.BreezeeUtils;
import com.kelai.utils.ContextUtil;
import org.olap4j.*;
import org.olap4j.metadata.Member;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.ImportResource;

import java.io.*;
import java.sql.DriverManager;
import java.text.ParseException;
import java.util.*;

/**
 * 服务启动
 */
@Configuration
@EnableAutoConfiguration
@ComponentScan("com.kelai")
@ImportResource(value = {"classpath:/server-provider.xml" , "classpath:/server-quartz.xml", "classpath:/spring-redis.xml"})
public class BootStartup {

    public static void main(String[] args) throws IOException {
        ContextUtil.current = SpringApplication.run(BootStartup.class, args);
        ContextUtil.current.getBeansOfType(IResourceLayer.class).forEach((a, b) -> {
            b.initService();
        });
        ContextUtil.getBean("redisBean", RedisBean.class).initCustomers();
        //TODO 改用redis异步消息队列，抛弃原来的利用java集合缓存数据方式
//        ContextUtil.getBean("cacheBean", CacheBean.class).start();

//        test("0","10","1341","2017-01-01","2017-01-30");
    }

    /*private static void test(String startPage,String len,String shopId,String startTime,String endTime) {
        List<ShopDataDayEntity> l = new ArrayList<>();
        try {
            ICustomerRepository customerRepository = ContextUtil.getBean("customerRepository", ICustomerRepository.class);
            System.out.println("----");
            Class.forName("org.olap4j.driver.xmla.XmlaOlap4jDriver");
            OlapConnection con = (OlapConnection) DriverManager.getConnection("jdbc:xmla:Server=http://"+WebAppConstants.BI_LINK_IP+"/MDKL/msmdpump.dll;Catalog="+WebAppConstants.BI_LINK_NAME);
            OlapWrapper wrapper = (OlapWrapper) con;
            OlapConnection olapConnection = wrapper.unwrap(OlapConnection.class);
            OlapStatement stmt = olapConnection.createStatement();

            StringBuffer strBuffer = new StringBuffer();
            strBuffer.append(" ");
            strBuffer.append(" SELECT NON EMPTY { [Measures].[进], [Measures].[出] } ON 0, ");
            strBuffer.append(" NON EMPTY {  subset ");
            strBuffer.append(" (NONEMPTY( ");
            strBuffer.append(" [设备].[客户设备ID].[客户ID].ALLMEMBERS *  ");
            strBuffer.append(" [时间].[年].[年].ALLMEMBERS * ");
            strBuffer.append(" [时间].[月].[月].ALLMEMBERS *  ");
            strBuffer.append(" [时间].[日].[日].ALLMEMBERS ) ");
            strBuffer.append(" ,"+startPage+" ,"+len+")} on 1 ");
            strBuffer.append(" FROM ( SELECT ( { [时间].[年月日小时].[日].&["+startTime+"T00:00:00]:[时间].[年月日小时].[日].&["+endTime+"T00:00:00] }, ");
            strBuffer.append(" { [设备].[客户设备ID].[客户ID].&["+shopId+"] } ) ON COLUMNS ");
            strBuffer.append(" FROM [客流]) ");

            CellSet cellSet = stmt.executeOlapQuery(strBuffer.toString());


            System.out.println(cellSet.getAxes().size());
            for (Position rowPos : cellSet.getAxes().get(1)) {
                String s = "";
                for (Member member : rowPos.getMembers()) {
                    s += member.getCaption() + "*";
                }
                for (Position colPos : cellSet.getAxes().get(0)) {
                    Cell cell = cellSet.getCell(colPos, rowPos);
                    s += cell.getFormattedValue() + "*";
                }
                String[] ss = s.split("\\*");
                String id = ss[0] + "-" + ss[1] + ss[2] + ss[3];
                ShopDataDayEntity sdde = new ShopDataDayEntity();
                sdde.setId(id);
                sdde.setCode(id);
                CustomerEntity ce = customerRepository.findOne(ss[0]);
                sdde.setCustomer(ce);
                sdde.setCustomerId(ce.getParentId());
                sdde.setHappenTime(BreezeeUtils.DATE_FORMAT_SHORT.parse(ss[1] + "-" + ss[2] + "-" + ss[3]));
                sdde.setYear(Integer.parseInt(ss[1]));
                sdde.setDxin(Integer.parseInt(ss[4]));
                sdde.setDxout(Integer.parseInt(ss[5]));
                l.add(sdde);
            }
        } catch (ParseException e1) {
            e1.printStackTrace();
        } catch (OlapException e1) {
            e1.printStackTrace();
        } catch (SQLException e1) {
            e1.printStackTrace();
        } catch (ClassNotFoundException e1) {
            e1.printStackTrace();
        }

        System.out.println(" 客户       门店       设备名称 设备位置 进店数 出店数 时间");
        l.forEach( shopDayData -> {
            System.out.println( shopDayData.getCustomerName() + " "+shopDayData.getShopName() + "     --    --      " + shopDayData.getDxin() + " "+shopDayData.getDxout() + " " +shopDayData.getHappenTime());
        });


    }
*/

    /**
     * 粒度日，按店铺汇总
     */
   /* private static void shopDay() {
        try {
            IShopDataDayRepository shopDataDayRepository = ContextUtil.getBean("shopDataDayRepository", IShopDataDayRepository.class);
            ICustomerRepository customerRepository = ContextUtil.getBean("customerRepository", ICustomerRepository.class);
            System.out.println("----");
            Class.forName("org.olap4j.driver.xmla.XmlaOlap4jDriver");
            OlapConnection con = (OlapConnection) DriverManager.getConnection("jdbc:xmla:Server=http://"+WebAppConstants.BI_LINK_IP+"/MDKL/msmdpump.dll;Catalog="+WebAppConstants.BI_LINK_NAME);
            OlapWrapper wrapper = (OlapWrapper) con;
            OlapConnection olapConnection = wrapper.unwrap(OlapConnection.class);
            OlapStatement stmt = olapConnection.createStatement();

            CellSet cellSet = stmt.executeOlapQuery("SELECT NON EMPTY { [Measures].[进], [Measures].[出] } ON COLUMNS, \n" +
                    " NON EMPTY { ([设备].[客户设备].[客户名称].ALLMEMBERS * \n" +
                    " [时间].[年].[年].ALLMEMBERS * [时间].[月].[月].ALLMEMBERS * [时间].[日].[日].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS \n" +
                    " FROM [客流]");
            List<DeviceDataEntity> l = new ArrayList<>();
            System.out.println(cellSet.getAxes().size());
            for (Position rowPos : cellSet.getAxes().get(1)) {
                String s = "";
                for (Member member : rowPos.getMembers()) {
                    s += member.getCaption() + "*";
                }
                for (Position colPos : cellSet.getAxes().get(0)) {
                    Cell cell = cellSet.getCell(colPos, rowPos);
                    s += cell.getFormattedValue() + "*";
                }
                String[] ss = s.split("\\*");
                String id = ss[0] + "-" + ss[1] + ss[2] + ss[3];
                ShopDataDayEntity sdde = shopDataDayRepository.findOne(id);
                if (sdde == null) {
                    sdde = new ShopDataDayEntity();
                    sdde.setId(id);
                    sdde.setCode(id);
                    CustomerEntity ce = customerRepository.findOne(ss[0]);
                    if (ce == null)
                        continue;
                    sdde.setCustomer(ce);
                    sdde.setCustomerId(ce.getParentId());
                    try {
                        sdde.setHappenTime(BreezeeUtils.DATE_FORMAT_SHORT.parse(ss[1] + "-" + ss[2] + "-" + ss[3]));
                    } catch (ParseException e) {
                        e.printStackTrace();
                    }
                    sdde.setYear(Integer.parseInt(ss[1]));
                    sdde.setDxin(Integer.parseInt(ss[4]));
                    sdde.setDxout(Integer.parseInt(ss[5]));
                    shopDataDayRepository.save(sdde);
                }
            }


        } catch (Exception e) {
            e.printStackTrace();
        }
    }*/

    /**
     * 粒度周，按店铺汇总
     */
    private static void shopWeek() {
        try {
            IShopDataWeekRepository shopDataWeekRepository = ContextUtil
                    .getBean("shopDataWeekRepository", IShopDataWeekRepository.class);
            ICustomerRepository customerRepository = ContextUtil
                    .getBean("customerRepository", ICustomerRepository.class);

            System.out.println("----");
            Class.forName("org.olap4j.driver.xmla.XmlaOlap4jDriver");
            OlapConnection con = (OlapConnection) DriverManager
                    .getConnection("jdbc:xmla:Server=http://"+WebAppConstants.BI_LINK_IP+"/MDKL/msmdpump.dll;Catalog="+WebAppConstants.BI_LINK_NAME);
            OlapWrapper wrapper = (OlapWrapper) con;
            OlapConnection olapConnection = wrapper.unwrap(OlapConnection.class);
            OlapStatement stmt = olapConnection.createStatement();

            StringBuffer strBuffer = new StringBuffer();
            strBuffer.append(" SELECT NON EMPTY { [Measures].[进], [Measures].[出] } ON COLUMNS, ");
            strBuffer.append(" NON EMPTY { ");
            strBuffer.append(" ([设备].[客户设备ID].[客户ID].ALLMEMBERS * ");
            strBuffer.append(" [时间].[年].[年].ALLMEMBERS *  ");
            strBuffer.append(" [时间].[周].[周].ALLMEMBERS  ) } ");
            strBuffer.append(" DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS ");
            strBuffer.append(" FROM ( SELECT ( { [时间].[年月日小时].[年].&[2017] } ) ON COLUMNS ");
            strBuffer.append(" FROM [客流]) WHERE ( [时间].[年月日小时].[年].&[2017] ) ");

            CellSet cellSet = stmt.executeOlapQuery(strBuffer.toString());
            System.out.println(cellSet.getAxes().size());
            for (Position rowPos : cellSet.getAxes().get(1)) {
                String s = "";
                for (Member member : rowPos.getMembers()) {
                    s += member.getCaption() + "*";
                }
                for (Position colPos : cellSet.getAxes().get(0)) {
                    Cell cell = cellSet.getCell(colPos, rowPos);
                    s += cell.getFormattedValue() + "*";
                }
                String[] ss = s.split("\\*");
                String id = ss[0] + "-" + ss[1] + ss[2];
                ShopDataWeekEntity sdwe = shopDataWeekRepository.findOne(id);
                if (sdwe == null) {
                    sdwe = new ShopDataWeekEntity();
                    sdwe.setId(id);
                    sdwe.setCode(id);
                    CustomerEntity ce = customerRepository.findOne(ss[0]);
                    if (ce == null)
                        continue;
                    sdwe.setCustomer(ce);
                    sdwe.setCustomerId(ce.getParentId());

                    //将某周的周一转化为时间
//                    Calendar cal = Calendar.getInstance();
//                    cal.set(Calendar.YEAR, Integer.valueOf(ss[1]));
//                    cal.set(Calendar.WEEK_OF_YEAR, Integer.valueOf(ss[2]));
//                    cal.set(Calendar.DAY_OF_WEEK, 2); // 1表示周日，2表示周一，7表示周六
//                    Date date = cal.getTime();
                    sdwe.setHappenTime(ss[2]);

                    sdwe.setYear(Integer.parseInt(ss[1]));
                    sdwe.setDxin(Integer.parseInt(ss[3]));
                    sdwe.setDxout(Integer.parseInt(ss[4]));
                    shopDataWeekRepository.save(sdwe);
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 粒度月，按店铺汇总
     */
    private static void shopMonth() {
        try {
            IShopDataMonthRepository shopDataMonthRepository = ContextUtil
                    .getBean("shopDataMonthRepository", IShopDataMonthRepository.class);
            ICustomerRepository customerRepository = ContextUtil
                    .getBean("customerRepository", ICustomerRepository.class);

            System.out.println("----");
            Class.forName("org.olap4j.driver.xmla.XmlaOlap4jDriver");
            OlapConnection con = (OlapConnection) DriverManager
                    .getConnection("jdbc:xmla:Server=http://"+ WebAppConstants.BI_LINK_IP+"/MDKL/msmdpump.dll;Catalog="+WebAppConstants.BI_LINK_NAME);
            OlapWrapper wrapper = (OlapWrapper) con;
            OlapConnection olapConnection = wrapper.unwrap(OlapConnection.class);
            OlapStatement stmt = olapConnection.createStatement();

            StringBuffer strBuffer = new StringBuffer();
            strBuffer.append(" SELECT NON EMPTY { [Measures].[进], [Measures].[出] } ON COLUMNS, ");
            strBuffer.append(" NON EMPTY { ");
            strBuffer.append(" ([设备].[客户设备ID].[客户ID].ALLMEMBERS * ");
            strBuffer.append(" [时间].[年].[年].ALLMEMBERS * ");
            strBuffer.append(" [时间].[月].[月].ALLMEMBERS ) } ");
            strBuffer.append(" DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS ");
            strBuffer.append(" FROM ( SELECT ( { [时间].[年月日小时].[年].&[2017] } ) ON COLUMNS ");
            strBuffer.append(" FROM [客流]) WHERE ( [时间].[年月日小时].[年].&[2017] ) ");

            CellSet cellSet = stmt.executeOlapQuery(strBuffer.toString());
            System.out.println(cellSet.getAxes().size());
            for (Position rowPos : cellSet.getAxes().get(1)) {
                String s = "";
                for (Member member : rowPos.getMembers()) {
                    s += member.getCaption() + "*";
                }
                for (Position colPos : cellSet.getAxes().get(0)) {
                    Cell cell = cellSet.getCell(colPos, rowPos);
                    s += cell.getFormattedValue() + "*";
                }
                String[] ss = s.split("\\*");
                String id = ss[0] + "-" + ss[1] + ss[2];
                ShopDataMonthEntity sdme = shopDataMonthRepository.findOne(id);
                if (sdme == null) {
                    sdme = new ShopDataMonthEntity();
                    sdme.setId(id);
                    sdme.setCode(id);
                    CustomerEntity ce = customerRepository.findOne(ss[0]);
                    if (ce == null)
                        continue;
                    sdme.setCustomer(ce);
                    sdme.setCustomerId(ce.getParentId());
                    try {
                        sdme.setHappenTime(BreezeeUtils.DATE_FORMAT_MONTH.parse(ss[1] + "-" + ss[2]));
                    } catch (ParseException e) {
                        e.printStackTrace();
                    }
                    sdme.setYear(Integer.parseInt(ss[1]));
                    sdme.setDxin(Integer.parseInt(ss[3]));
                    sdme.setDxout(Integer.parseInt(ss[4]));
                    shopDataMonthRepository.save(sdme);
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 粒度小时，按店铺汇总
     */
    private static void shopHour() {
        try {
            System.out.println("----");
            Class.forName("org.olap4j.driver.xmla.XmlaOlap4jDriver");
            OlapConnection con = (OlapConnection) DriverManager.getConnection("jdbc:xmla:Server=http://"+WebAppConstants.BI_LINK_IP+"/MDKL/msmdpump.dll;Catalog="+WebAppConstants.BI_LINK_NAME);
            OlapWrapper wrapper = (OlapWrapper) con;
            OlapConnection olapConnection = wrapper.unwrap(OlapConnection.class);
            OlapStatement stmt = olapConnection.createStatement();

            CellSet cellSet = stmt.executeOlapQuery("SELECT NON EMPTY { [Measures].[进], [Measures].[出] } ON COLUMNS, \n" +
                    " NON EMPTY { ([设备].[客户设备].[客户名称].ALLMEMBERS * \n" +
                    " [时间].[年].[年].ALLMEMBERS * [时间].[月].[月].ALLMEMBERS * [时间].[日].[日].ALLMEMBERS * [时间].[小时].[小时].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS \n" +
                    " FROM ( SELECT ( { [时间].[年月日小时].[年].&[2015] } ) ON COLUMNS FROM [客流]) WHERE ( [时间].[年月日小时].[年].&[2015] )");
            List<DeviceDataEntity> l = new ArrayList<>();
            System.out.println(cellSet.getAxes().size() + "++++++++++++");
            IShopDataHourRepoistory shopDataHourRepoistory = ContextUtil.getBean("shopDataHourRepoistory", IShopDataHourRepoistory.class);
            ICustomerRepository customerRepository = ContextUtil.getBean("customerRepository", ICustomerRepository.class);
            for (Position rowPos : cellSet.getAxes().get(1)) {
                String s = "";
                for (Member member : rowPos.getMembers()) {
                    s += member.getCaption() + "*";
                }
                for (Position colPos : cellSet.getAxes().get(0)) {

                    Cell cell = cellSet.getCell(colPos, rowPos);
                    s += cell.getFormattedValue() + "*";
                }
                System.out.println(s);
                String[] ss = s.split("\\*");
                String id = ss[0] + "-" + ss[1] + ss[2] + ss[3] + ss[4];
                ShopDataHourEntity sdde = new ShopDataHourEntity();
                sdde.setId(id);
                sdde.setCode(id);
                CustomerEntity ce = customerRepository.findOne(ss[0]);
                if (ce == null)
                    continue;
                sdde.setCustomer(ce);
                sdde.setCustomerId(ce.getParentId());
                try {
                    sdde.setHappenTime(BreezeeUtils.DATE_FORMAT_LONG.parse(ss[1] + "-" + ss[2] + "-" + ss[3] + " " + ss[4] + ":00:00"));
                } catch (ParseException e) {
                    e.printStackTrace();
                }
                sdde.setYear(Integer.parseInt(ss[1]));
                sdde.setDxin(Integer.parseInt(ss[5]));
                sdde.setDxout(Integer.parseInt(ss[6]));
                shopDataHourRepoistory.save(sdde);
            }

            System.out.println("************end");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 粒度小时，按设备汇总
     */
    private static void deviceHour() {
        try {
            System.out.println("----");
            Class.forName("org.olap4j.driver.xmla.XmlaOlap4jDriver");
            OlapConnection con = (OlapConnection) DriverManager.getConnection("jdbc:xmla:Server=http://"+WebAppConstants.BI_LINK_IP+"/MDKL/msmdpump.dll;Catalog="+WebAppConstants.BI_LINK_NAME);
            OlapWrapper wrapper = (OlapWrapper) con;
            OlapConnection olapConnection = wrapper.unwrap(OlapConnection.class);
            OlapStatement stmt = olapConnection.createStatement();

            CellSet cellSet = stmt.executeOlapQuery("SELECT NON EMPTY { [Measures].[进], [Measures].[出] } ON COLUMNS, \n" +
                    " NON EMPTY { ([设备].[客户ID].[客户ID].ALLMEMBERS * [设备].[设备ID].[设备ID].ALLMEMBERS * \n" +
                    " [时间].[年].[年].ALLMEMBERS * [时间].[月].[月].ALLMEMBERS * [时间].[日].[日].ALLMEMBERS * " +
                    "[时间].[小时].[小时].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS \n" +
                    " FROM ( SELECT ( { [时间].[年月日小时].[年].&[2017] } ) ON COLUMNS FROM [客流]) WHERE ( [时间].[年月日小时].[年].&[2017] )");
            List<DeviceDataEntity> l = new ArrayList<>();
            IDeviceDataHourRepository deviceDataHourRepository = ContextUtil.getBean("deviceDataHourRepository", IDeviceDataHourRepository.class);
            ICustomerRepository customerRepository = ContextUtil.getBean("customerRepository", ICustomerRepository.class);
            IDeviceRepository deviceRepository = ContextUtil.getBean("deviceRepository", IDeviceRepository.class);
            for (Position rowPos : cellSet.getAxes().get(1)) {
                String s = "";
                for (Member member : rowPos.getMembers()) {
                    s += member.getCaption() + "*";
                }
                for (Position colPos : cellSet.getAxes().get(0)) {

                    Cell cell = cellSet.getCell(colPos, rowPos);
                    s += cell.getFormattedValue() + "*";
                }
                System.out.println(s);
                String[] ss = s.split("\\*");
                String id = ss[0] + "-" + ss[1] + "-" + ss[2] + ss[3] + ss[4] + ss[5];
                DeviceDataHourEntity sdde = new DeviceDataHourEntity();
                sdde.setId(id);
                sdde.setCode(id);
                System.out.println(id);
                CustomerEntity ce = customerRepository.findOne(ss[0]);
                if (ce == null) {
                    System.out.println("no customer"+ss[0]);
                    continue;
                }
                sdde.setCustomer(ce);
                sdde.setCustomerId(ce.getParentId());
                DeviceEntity de = deviceRepository.findOne(ss[1]);
                if (de == null)
                    continue;
                sdde.setDevice(de);
                try {
                    sdde.setHappenTime(BreezeeUtils.DATE_FORMAT_LONG.parse(ss[2] + "-" + ss[3] + "-" + ss[4] + " " + ss[5] + ":00:00"));
                } catch (ParseException e) {
                    e.printStackTrace();
                }
                sdde.setYear(Integer.parseInt(ss[2]));
                sdde.setDxin(Integer.parseInt(ss[6]));
                sdde.setDxout(Integer.parseInt(ss[7]));
                deviceDataHourRepository.save(sdde);
            }
            System.out.println("************end");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 粒度周，按设备汇总
     */
    private static void deviceWeek() {
        try {
            System.out.println("----");
            Class.forName("org.olap4j.driver.xmla.XmlaOlap4jDriver");
            OlapConnection con = (OlapConnection) DriverManager
                    .getConnection("jdbc:xmla:Server=http://"+WebAppConstants.BI_LINK_IP+"/MDKL/msmdpump.dll;Catalog="+WebAppConstants.BI_LINK_NAME);
            OlapWrapper wrapper = (OlapWrapper) con;
            OlapConnection olapConnection = wrapper.unwrap(OlapConnection.class);
            OlapStatement stmt = olapConnection.createStatement();

            StringBuffer strBuffer = new StringBuffer();
            strBuffer.append(" SELECT NON EMPTY { [Measures].[进], [Measures].[出] } ON COLUMNS, ");
            strBuffer.append(" NON EMPTY { ");
            strBuffer.append(" ([设备].[客户ID].[客户ID].ALLMEMBERS * ");
            strBuffer.append(" [设备].[设备ID].[设备ID].ALLMEMBERS *  ");
            strBuffer.append(" [时间].[年].[年].ALLMEMBERS *  ");
            strBuffer.append(" [时间].[周].[周].ALLMEMBERS) } ");
            strBuffer.append(" DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS ");
            strBuffer.append(" FROM ( ");
            strBuffer.append(" SELECT ( { [时间].[年月日小时].[年].&[2017] } ) ");
            strBuffer.append(" ON COLUMNS FROM [客流]) WHERE ( [时间].[年月日小时].[年].&[2017] ) ");

            CellSet cellSet = stmt.executeOlapQuery(strBuffer.toString());

            IDeviceDataWeekRepository deviceDataWeekRepository = ContextUtil
                    .getBean("deviceDataWeekRepository", IDeviceDataWeekRepository.class);
            ICustomerRepository customerRepository = ContextUtil
                    .getBean("customerRepository", ICustomerRepository.class);
            IDeviceRepository deviceRepository = ContextUtil
                    .getBean("deviceRepository", IDeviceRepository.class);
            for (Position rowPos : cellSet.getAxes().get(1)) {
                String s = "";
                for (Member member : rowPos.getMembers()) {
                    s += member.getCaption() + "*";
                }
                for (Position colPos : cellSet.getAxes().get(0)) {

                    Cell cell = cellSet.getCell(colPos, rowPos);
                    s += cell.getFormattedValue() + "*";
                }
                System.out.println(s);
                String[] ss = s.split("\\*");
                String id = ss[0] + "-" + ss[1] + "-" + ss[2] + ss[3] ;
                DeviceDataWeekEntity ddwe = new DeviceDataWeekEntity();
                ddwe.setId(id);
                ddwe.setCode(id);
                System.out.println(id);
                CustomerEntity ce = customerRepository.findOne(ss[0]);
                if (ce == null) {
                    System.out.println("no customer"+ss[0]);
                    continue;
                }
                ddwe.setCustomer(ce);
                ddwe.setCustomerId(ce.getParentId());
                DeviceEntity de = deviceRepository.findOne(ss[1]);
                if (de == null)
                    continue;
                ddwe.setDevice(de);
                try {
                    //将某周的周一转化为时间
                    Calendar cal = Calendar.getInstance();
                    cal.set(Calendar.YEAR, Integer.valueOf(ss[2]));
                    cal.set(Calendar.WEEK_OF_YEAR, Integer.valueOf(ss[3]));
                    cal.set(Calendar.DAY_OF_WEEK, 2); // 1表示周日，2表示周一，7表示周六
                    Date date = cal.getTime();
//                    ddwe.setHappenTime(date);
//                    ddwe.setHappenTime(BreezeeUtils.DATE_FORMAT_LONG.parse(ss[2] + "-" + ss[3] + "-" + ss[4] + " " + ss[5] + ":00:00"));
                } catch (Exception e) {
                    e.printStackTrace();
                }
                ddwe.setYear(Integer.parseInt(ss[2]));
                ddwe.setDxin(Integer.parseInt(ss[4]));
                ddwe.setDxout(Integer.parseInt(ss[5]));
                deviceDataWeekRepository.save(ddwe);
            }
            System.out.println("************end");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 粒度月，按设备汇总
     */
    private static void deviceMonth() {
        try {
            System.out.println("----");
            Class.forName("org.olap4j.driver.xmla.XmlaOlap4jDriver");
            OlapConnection con = (OlapConnection) DriverManager
                    .getConnection("jdbc:xmla:Server=http://"+WebAppConstants.BI_LINK_IP+"/MDKL/msmdpump.dll;Catalog="+WebAppConstants.BI_LINK_NAME);
            OlapWrapper wrapper = (OlapWrapper) con;
            OlapConnection olapConnection = wrapper.unwrap(OlapConnection.class);
            OlapStatement stmt = olapConnection.createStatement();

            StringBuffer strBuffer = new StringBuffer();
            strBuffer.append(" ");
            strBuffer.append(" SELECT NON EMPTY { [Measures].[进], [Measures].[出] } ON COLUMNS, ");
            strBuffer.append(" NON EMPTY { ");
            strBuffer.append(" ([设备].[客户ID].[客户ID].ALLMEMBERS * ");
            strBuffer.append(" [设备].[设备ID].[设备ID].ALLMEMBERS *  ");
            strBuffer.append(" [时间].[年].[年].ALLMEMBERS *  ");
            strBuffer.append(" [时间].[月].[月].ALLMEMBERS ) } ");
            strBuffer.append(" DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS ");
            strBuffer.append(" FROM ( ");
            strBuffer.append(" SELECT ( { [时间].[年月日小时].[年].&[2017] } ) ");
            strBuffer.append(" ON COLUMNS FROM [客流]) WHERE ( [时间].[年月日小时].[年].&[2017] ) ");

            CellSet cellSet = stmt.executeOlapQuery(strBuffer.toString());
            IDeviceDataMonthRepository deviceDataMonthRepository = ContextUtil
                    .getBean("deviceDataMonthRepository", IDeviceDataMonthRepository.class);
            ICustomerRepository customerRepository = ContextUtil
                    .getBean("customerRepository", ICustomerRepository.class);
            IDeviceRepository deviceRepository = ContextUtil
                    .getBean("deviceRepository", IDeviceRepository.class);
            for (Position rowPos : cellSet.getAxes().get(1)) {
                String s = "";
                for (Member member : rowPos.getMembers()) {
                    s += member.getCaption() + "*";
                }
                for (Position colPos : cellSet.getAxes().get(0)) {

                    Cell cell = cellSet.getCell(colPos, rowPos);
                    s += cell.getFormattedValue() + "*";
                }
                System.out.println(s);
                String[] ss = s.split("\\*");
                String id = ss[0] + "-" + ss[1] + "-" + ss[2] + ss[3];
                DeviceDataMonthEntity ddme = new DeviceDataMonthEntity();
                ddme.setId(id);
                ddme.setCode(id);
                System.out.println(id);
                CustomerEntity ce = customerRepository.findOne(ss[0]);
                if (ce == null) {
                    System.out.println("no customer"+ss[0]);
                    continue;
                }
                ddme.setCustomer(ce);
                ddme.setCustomerId(ce.getParentId());
                DeviceEntity de = deviceRepository.findOne(ss[1]);
                if (de == null)
                    continue;
                ddme.setDevice(de);
                try {
                    ddme.setHappenTime(BreezeeUtils.DATE_FORMAT_MONTH.parse(ss[2] + "-" + ss[3]));
                } catch (ParseException e) {
                    e.printStackTrace();
                }
                ddme.setYear(Integer.parseInt(ss[2]));
                ddme.setDxin(Integer.parseInt(ss[4]));
                ddme.setDxout(Integer.parseInt(ss[5]));
                deviceDataMonthRepository.save(ddme);
            }
            System.out.println("************end");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 粒度日，按设备汇总
     */
    private static void deviceDay() {
        try {
            System.out.println("----");
            Class.forName("org.olap4j.driver.xmla.XmlaOlap4jDriver");
            OlapConnection con = (OlapConnection) DriverManager.getConnection("jdbc:xmla:Server=http://"+WebAppConstants.BI_LINK_IP+"/MDKL/msmdpump.dll;Catalog="+WebAppConstants.BI_LINK_NAME);
            OlapWrapper wrapper = (OlapWrapper) con;
            OlapConnection olapConnection = wrapper.unwrap(OlapConnection.class);
            OlapStatement stmt = olapConnection.createStatement();
            if(stmt==null) {
                System.out.println("Can't connect to the olap server...");
                return;
            }
            CellSet cellSet = stmt.executeOlapQuery("SELECT NON EMPTY { [Measures].[进], [Measures].[出] } ON COLUMNS, \n" +
                    " NON EMPTY { ([设备].[客户名称].[客户名称].ALLMEMBERS * [设备].[设备名称].[设备名称].ALLMEMBERS * \n" +
                    " [时间].[年].[年].ALLMEMBERS * [时间].[月].[月].ALLMEMBERS * [时间].[日].[日].ALLMEMBERS) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS \n" +
                    " FROM ( SELECT ( { [时间].[年月日小时].[年].&[2017] } ) ON COLUMNS FROM [客流]) WHERE ( [时间].[年月日小时].[年].&[2017] )");
            List<DeviceDataEntity> l = new ArrayList<>();
            IDeviceDataDayRepository deviceDataDayRepository = ContextUtil.getBean("deviceDataDayRepository", IDeviceDataDayRepository.class);
            ICustomerRepository customerRepository = ContextUtil.getBean("customerRepository", ICustomerRepository.class);
            IDeviceRepository deviceRepository = ContextUtil.getBean("deviceRepository", IDeviceRepository.class);
            for (Position rowPos : cellSet.getAxes().get(1)) {
                String s = "";
                for (Member member : rowPos.getMembers()) {
                    s += member.getCaption() + "*";
                }
                for (Position colPos : cellSet.getAxes().get(0)) {

                    Cell cell = cellSet.getCell(colPos, rowPos);
                    s += cell.getFormattedValue() + "*";
                }
                System.out.println(s);
                String[] ss = s.split("\\*");
                String id = ss[0] + "-" + ss[1] + "-" + ss[2] + ss[3] + ss[4];
                DeviceDataDayEntity sdde = new DeviceDataDayEntity();
                sdde.setId(id);
                sdde.setCode(id);
                CustomerEntity ce = customerRepository.findOne(ss[0]);
                if (ce == null)
                    continue;
                sdde.setCustomer(ce);
                sdde.setCustomerId(ce.getParentId());
                DeviceEntity de = deviceRepository.findOne(ss[1]);
                if (de == null)
                    continue;
                sdde.setDevice(de);
                try {
                    sdde.setHappenTime(BreezeeUtils.DATE_FORMAT_LONG.parse(ss[2] + "-" + ss[3] + "-" + ss[4] + " " + "00:00:00"));
                } catch (ParseException e) {
                    e.printStackTrace();
                }
                sdde.setYear(Integer.parseInt(ss[2]));
                sdde.setDxin(Integer.parseInt(ss[5]));
                sdde.setDxout(Integer.parseInt(ss[6]));
                deviceDataDayRepository.save(sdde);
            }
            System.out.println("************end");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

}
